﻿using DataAccessService.IService;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using DataAccessService.Repository;
using DataAccessService.Repository.MySQL;
using DataAccessService.Model;
using System.Data;

namespace DataAccessService.Service
{
    public class MySqlService : IMySqlService
    {
        MySQLExcuteRepository mySQLExcuteRepository = new MySQLExcuteRepository();

        public MySql.Data.MySqlClient.MySqlConnection GetConn()
        {
            MySQLConnectRepository mySQLConnectRepository = new MySQLConnectRepository();

            ISqliteService sqliteService = new SqliteService();
            List<SysConfig> items = sqliteService.GetList<SysConfig>("type='MYSQL'");
            SysConfig item = items.FirstOrDefault();

            string connStr = string.Format("database={0};Password={1};User ID={2};server={3};charset=utf8", item.DataBase, item.Psw, item.UserId, item.Server);

            return mySQLConnectRepository.GetConn(connStr);
        }

        public DataSet QuerySQL(string sql)
        {
            MySql.Data.MySqlClient.MySqlConnection conn = GetConn();
            DataSet ds = mySQLExcuteRepository.ExcuteQuerySQL(conn, sql);
            
            if (conn != null)
                conn.Close();

            return ds;
        }

        public bool ExcuteSQL(string sql)
        {
            MySql.Data.MySqlClient.MySqlConnection conn = GetConn();
            bool result = mySQLExcuteRepository.ExcuteSQL(conn, sql);
            conn.Close();

            return result;
        }

        public T GetItem<T>(string sql)
        {
            MySql.Data.MySqlClient.MySqlConnection conn = GetConn();
            var dataset = mySQLExcuteRepository.ExcuteQuerySQL(conn, sql);
            conn.Close();

            List<T> list = mySQLExcuteRepository.ConvertToEntity<T>(dataset);
            if (list.Any())
                return list.SingleOrDefault();

            return default(T);
        }

        public List<T> GetList<T>(string sql)
        {
            MySql.Data.MySqlClient.MySqlConnection conn = GetConn();
            var dataset = mySQLExcuteRepository.ExcuteQuerySQL(conn, sql);
            if (conn != null)
                conn.Close();

            return mySQLExcuteRepository.ConvertToEntity<T>(dataset);
        }

        public bool Add<T>(T item, string field)
        {
            MySql.Data.MySqlClient.MySqlConnection conn = GetConn();

            List<string> columns = GetColumnNames(conn, typeof(T).Name);
            string sql = mySQLExcuteRepository.GetInsertSql<T>(item, field, columns);
            return ExcuteSQL(sql);
        }

        public bool Add<T>(MySqlConnection conn, MySqlTransaction tran, T item, string field)
        {
            List<string> columns = GetColumnNames(conn, typeof(T).Name);
            string sql = mySQLExcuteRepository.GetInsertSql<T>(item, field, columns);
            return mySQLExcuteRepository.ExcuteSQL(conn, tran, sql);
        }

        public bool Update<T>(T item, string field, string id)
        {
            MySql.Data.MySqlClient.MySqlConnection conn = GetConn();

            List<string> columns = GetColumnNames(conn, typeof(T).Name);

            string sql = mySQLExcuteRepository.GetUpdateSql<T>(item, field, id, columns);
            bool result = mySQLExcuteRepository.ExcuteSQL(conn, sql);
            conn.Close();

            return result;
        }

        public bool AddItems<T>(List<T> items, string idField)
        {
            MySql.Data.MySqlClient.MySqlConnection conn = GetConn();
            using (MySqlTransaction tran = conn.BeginTransaction())
            {
                foreach (T item in items)
                {
                    if (!Add(conn, tran, item, idField))
                    {
                        tran.Rollback();
                        conn.Close();
                        return false;
                    }
                }
                tran.Commit();
                conn.Close();

                return true;
            }
        }

        /// <summary>
        /// 获取表格字段集合
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private List<string> GetColumnNames(MySqlConnection conn, string tableName)
        {
            string sql = string.Format("select * from {0} where 1=2", tableName);
            DataSet dataSet = mySQLExcuteRepository.ExcuteQuerySQL(conn, sql);

            List<string> columnsList = new List<string>();
            foreach (DataColumn column in dataSet.Tables[0].Columns)
            {
                columnsList.Add(column.ColumnName);
            }
            return columnsList;
        }

    }
}
